package com.me.db;

import java.util.ArrayList;
import java.util.TimeZone;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import com.me.data.MeEvent;
import com.me.db.MeDB.LocationTable;
import com.me.db.MeDB.TitleTable;
import com.me.util.AppConstant;
import com.me.util.AppUtil;

public class MeDbManager {
	SQLiteDatabase dbRead;
	SQLiteDatabase dbWrite;
	MeDBHelper dbHelper;
	
	public MeDbManager(Context context) {

		dbHelper = new MeDBHelper(context);
		dbRead = dbHelper.getReadableDatabase();
		dbWrite = dbHelper.getWritableDatabase();
	}

// ***************************************************************************************************************
// Fetch Table
	
	public Cursor fetchEvents() {
		Cursor cursor = null;
		cursor = dbRead.query(MeDB.EventTable.EVENT_TABLE_NAME,
				MeQuery.PROJECTION, null, null, null, null,
				MeQuery.COLUMN_START + MeQuery.ORDER_ASCENDING);
		return cursor;
	}

	
	public Cursor fetchTitles() {
		Cursor cursor = null;
		cursor = dbRead.query(MeDB.TitleTable.TITLE_TABLE_NAME,
				MeQuery.T_PROJECTION, null, null, null, null,
				TitleTable.TITLE_PREFIX + MeQuery.ORDER_ASCENDING);
		return cursor;
	}

	
	public Cursor fetchLocations() {
		Cursor cursor = null;
		cursor = dbRead.query(MeDB.LocationTable.LOCATION_TABLE_NAME,
				MeQuery.LO_PROJECTION, null, null, null, null,
				LocationTable.LOCATION_PREFIX + MeQuery.ORDER_ASCENDING);
		return cursor;
	}
	

	public Cursor getTitleNames() {
		Cursor cursor = null;
		cursor = dbRead.query(MeDB.TitleTable.TITLE_TABLE_NAME,
				new String[] {"DISTINCT("+ MeDB.LocationTable.LOCATION_NAME +")"},
				null, null, null, null,
				TitleTable.TITLE_PREFIX + MeQuery.ORDER_ASCENDING);
		return cursor;
	}

	
	public Cursor getLoactionNames() {
		Cursor cursor = null;
		cursor = dbRead.query(MeDB.LocationTable.LOCATION_TABLE_NAME,
				new String[] {"DISTINCT("+ MeDB.LocationTable.LOCATION_NAME +")"},
				null, null, null, null,
				LocationTable.LOCATION_PREFIX + MeQuery.ORDER_ASCENDING);
		return cursor;
	}
	
// Fetch Table	
// ***************************************************************************************************************
// Search Table
	
	public Cursor searchTitles(String[] prefixes) {
		
		StringBuffer inClauseBuffer = new StringBuffer();
		inClauseBuffer.append("(");
		for (int i = 1; i < prefixes.length; i++) { // 1 less than size
			inClauseBuffer.append("?,");
		}
		inClauseBuffer.append("?)");
		String inClause = inClauseBuffer.toString();


		String sql = "SELECT DISTINCT("+ MeDB.TitleTable.TITLE_NAME
						+") FROM "+ MeDB.TitleTable.TITLE_TABLE_NAME
						+ " WHERE "+ MeDB.TitleTable.TITLE_PREFIX + " in "+ inClause
						+ " ORDER BY "+ MeDB.TitleTable.TITLE_COUNT + " "+ MeQuery.ORDER_DESCENDING;
				
		Cursor cursor = dbRead.rawQuery(sql, prefixes);

		return cursor;
	}
	
	public Cursor searchLocations(String[] prefixes) {
		
		StringBuffer inClauseBuffer = new StringBuffer();
		inClauseBuffer.append("(");
		for (int i = 1; i < prefixes.length; i++) { // 1 less than size
			inClauseBuffer.append("?,");
		}
		inClauseBuffer.append("?)");
		String inClause = inClauseBuffer.toString();


		String sql = "SELECT DISTINCT("+ MeDB.LocationTable.LOCATION_NAME
						+") FROM "+ MeDB.LocationTable.LOCATION_TABLE_NAME
						+ " WHERE "+ MeDB.LocationTable.LOCATION_PREFIX + " in "+ inClause
						+ " ORDER BY "+ MeDB.LocationTable.LOCATION_COUNT + " "+ MeQuery.ORDER_DESCENDING;
				
		Cursor cursor = null;
		cursor = dbRead.rawQuery(sql, prefixes);

		return cursor;
	}
	
	
// Search Table
// ***************************************************************************************************************
// Insert single record
	
	
	public void insertOneEvent(String inputEventTitle, String inputEventVenue,
			int allDay, long inputEventStart, long inputEventEnd) {
		ContentValues values = new ContentValues();

		values.put(MeDB.EventTable.EVENT_TITLE, inputEventTitle);
		values.put(MeDB.EventTable.EVENT_VENUE, inputEventVenue);
		values.put(MeDB.EventTable.EVENT_ALL_DAY, allDay);
		values.put(MeDB.EventTable.EVENT_START, inputEventStart);
		values.put(MeDB.EventTable.EVENT_END, inputEventEnd);
		values.put(MeDB.EventTable.EVENT_CALENDAR_ID,AppUtil.App.currCalendarId);
		values.put(MeDB.EventTable.EVENT_TIME_ZONE, TimeZone.getDefault().getID());

		dbWrite.insert(MeDB.EventTable.EVENT_TABLE_NAME, null, values);		
	}

	public void inserOneTitle(String title) {
		
		if (title == null || title.length() == 0)
			return;
				
		if (title!= null && title.length() > 0) {
			ContentValues values = new ContentValues();
			values.put(MeDB.TitleTable.TITLE_PREFIX, title.substring(0, 1).toUpperCase());
			values.put(MeDB.TitleTable.TITLE_NAME, title);
	
		
			try {
				dbWrite.insertOrThrow(MeDB.TitleTable.TITLE_TABLE_NAME, null, values);
			}
			catch (SQLException e) {
				
				String sql = "UPDATE "+ MeDB.TitleTable.TITLE_TABLE_NAME +" SET "
							+ MeDB.TitleTable.TITLE_COUNT + " = "+ MeDB.TitleTable.TITLE_COUNT +" + 1 WHERE "
							+ MeDB.TitleTable.TITLE_NAME + " = ?";

				dbWrite.execSQL(sql, new String[]{ title });
			}

		}		
	}
	
	public void inserOneLocation(String venue) {
		if (venue!= null && venue.length() > 0) {
			ContentValues values = new ContentValues();
			values.put(MeDB.LocationTable.LOCATION_PREFIX, venue.substring(0, 1).toUpperCase());
			values.put(MeDB.LocationTable.LOCATION_NAME, venue);
				
			try {
				dbWrite.insertOrThrow(MeDB.LocationTable.LOCATION_TABLE_NAME, null, values);
			}
			catch (SQLException e) {
				
				String sql = "UPDATE "+ MeDB.LocationTable.LOCATION_TABLE_NAME +" SET "
							+ MeDB.LocationTable.LOCATION_COUNT + " = "+ MeDB.LocationTable.LOCATION_COUNT +" + 1 WHERE "
							+ MeDB.LocationTable.LOCATION_NAME + " = ?";

				dbWrite.execSQL(sql, new String[]{ venue });
			}
			
		}		
	}
	

	
	
// Insert single record
// ***************************************************************************************************************
// Insert multiple records

	public int insertEvents(ArrayList<MeEvent> eventList) {
		String sql = "INSERT INTO " + MeDB.EventTable.EVENT_TABLE_NAME + " ( "
				+ MeDB.EventTable.EVENT_TITLE +", "
				+ MeDB.EventTable.EVENT_VENUE +", "
				+ MeDB.EventTable.EVENT_ALL_DAY +", "
				+ MeDB.EventTable.EVENT_START +", "
				+ MeDB.EventTable.EVENT_END +", "
				+ MeDB.EventTable.EVENT_CALENDAR_ID +", "
				+ MeDB.EventTable.EVENT_TIME_ZONE				
				+" ) VALUES ( ?, ?, ?, ?, ?, ?, ? )";	
		
		try {
			dbWrite.beginTransactionNonExclusive();
			SQLiteStatement stmt = dbWrite.compileStatement(sql);
			int i = 0;
			while (i<eventList.size()) {
				MeEvent event = eventList.get(i);
				
				stmt.bindString(1, event.getTitle());
				stmt.bindString(2, event.getVenue());
				if (event.isAllDay())
					stmt.bindLong(3, 1);
				else
					stmt.bindLong(3, 0);
				stmt.bindLong(4, event.getCalStart().getTimeInMillis());
				stmt.bindLong(5, event.getEnd());
				stmt.bindLong(6, Long.parseLong(AppUtil.App.currCalendarId));
				stmt.bindString(7, TimeZone.getDefault().getID());
				
				stmt.execute();
	            stmt.clearBindings();
	            i++; 
			}
	        dbWrite.setTransactionSuccessful();
		}
	    catch (Exception e) { return -1; } 
	    finally { dbWrite.endTransaction(); }
			
		return eventList.size();
	}
	
	public int insertTitles(ArrayList<String> titles) {
		String sql = "INSERT INTO " + MeDB.TitleTable.TITLE_TABLE_NAME + " ( "
				+ MeDB.TitleTable.TITLE_PREFIX +", "
				+ MeDB.TitleTable.TITLE_NAME				
				+" ) VALUES ( ?, ?)";
		
		try {
			dbWrite.beginTransactionNonExclusive();
			SQLiteStatement stmt = dbWrite.compileStatement(sql);
			int i = 0;
			while (i<titles.size()) {
				String title = titles.get(i);
				stmt.bindString(1, title.substring(0, 1));
				stmt.bindString(2, title);
				stmt.execute();
		        stmt.clearBindings();
		        i++;
			}
	        dbWrite.setTransactionSuccessful();
		}
	    catch (Exception e) { return -1; } 
	    finally { dbWrite.endTransaction(); }

		return titles.size();
	}
	
	public int insertLocations(ArrayList<String> locations) {
		String sql = "INSERT INTO " + MeDB.LocationTable.LOCATION_TABLE_NAME + " ( "
				+ MeDB.LocationTable.LOCATION_PREFIX +", "
				+ MeDB.LocationTable.LOCATION_NAME				
				+" ) VALUES ( ?, ?)";
		
		try {
			dbWrite.beginTransactionNonExclusive();
			SQLiteStatement stmt = dbWrite.compileStatement(sql);
			int i = 0;
			while (i<locations.size()) {
				String location = locations.get(i); 
				stmt.bindString(1, location.substring(0, 1));
				stmt.bindString(2, location);
				stmt.execute();
		        stmt.clearBindings();
		        i++;
			}
	        dbWrite.setTransactionSuccessful();
		}
	    catch (Exception e) { return -1; } 
	    finally { dbWrite.endTransaction(); }

		return locations.size();
	}
	
// Insert multiple records
// ***************************************************************************************************************
// Delete records
	
	public int deleteEvents(String inClause, String[] eventIDs) {
		return dbWrite.delete(MeDB.EventTable.EVENT_TABLE_NAME, 
				MeDB.EventTable.EVENT_COLUMN_ID + " in " + inClause, 
				eventIDs);
	}
	
	public int deleteTitles(String inClause, String[] itemIDs) {
		return dbWrite.delete(MeDB.TitleTable.TITLE_TABLE_NAME, 
				MeDB.TitleTable.TITLE_ID + " in " + inClause, 
				itemIDs);
	}

	public int deleteLocations(String inClause, String[] itemIDs) {
		return dbWrite.delete(MeDB.LocationTable.LOCATION_TABLE_NAME, 
				MeDB.LocationTable.LOCATION_ID + " in " + inClause, 
				itemIDs);
	}
	
	
// Delete records
// ***************************************************************************************************************
// Others
	
	public int countEvents(){
		Cursor cursor = dbRead.rawQuery("select id from " + MeDB.EventTable.EVENT_TABLE_NAME, null);
		return cursor.getCount();
	}

	public void updateEvent(String eventID, String inputEventTitle, String inputEventVenue, int allDay, long inputEventStart, long inputEventEnd) {
		ContentValues values = new ContentValues();

		values.put(MeDB.EventTable.EVENT_TITLE, inputEventTitle);
		values.put(MeDB.EventTable.EVENT_VENUE, inputEventVenue);
		values.put(MeDB.EventTable.EVENT_ALL_DAY, allDay);
		values.put(MeDB.EventTable.EVENT_START, inputEventStart);
		values.put(MeDB.EventTable.EVENT_END, inputEventEnd);
		values.put(MeDB.EventTable.EVENT_CALENDAR_ID,AppUtil.App.currCalendarId);
		values.put(MeDB.EventTable.EVENT_TIME_ZONE, TimeZone.getDefault().getID());

		dbWrite.update(MeDB.EventTable.EVENT_TABLE_NAME, values, MeQuery.SELECTION, new String[]{ eventID });
	}

	
	public Cursor searchEvents(String field, String keyword) {
		Cursor cursor = null;
		cursor = dbRead.query(MeDB.EventTable.EVENT_TABLE_NAME,
				MeQuery.PROJECTION, 
				field + " LIKE ?", 
				new String[] { "%"+keyword+"%" },
				null, null,
				MeQuery.COLUMN_START + MeQuery.ORDER_ASCENDING);
		return cursor;
	}
}
